﻿using CodeGenerator.DBInfo.entity;
using CodeGenerator.Utils;
using MySql.Data.MySqlClient;
using Npgsql;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeGenerator.DBInfo.Impl
{
    /// <summary>
    /// PostgreSQL数据库信息获取类
    /// </summary>
    internal class OracleDBInfoGetter : AbstractDbInfoGetter<OracleConnection, OracleDataAdapter>, IDBInfoGetter
    {
        //数据列类型与Java类型映射
        readonly Dictionary<string, string> JavaTypeMappings = new Dictionary<string, string>
            {
                { "NUMBER", "Long" },
                { "BINARY_FLOAT", "Double" },
                { "BINARY_DOUBLE", "Double" },
                { "CHAR", "String" },
                { "VARCHAR2", "String" },
                { "NCHAR", "String" },
                { "NVARCHAR2", "String" },
                { "DATE", "LocalDateTime" },
                { "TIMESTAMP", "LocalDateTime" },
                { "TIMESTAMP WITH TIME ZONE", "LocalDateTime" },
                { "TIMESTAMP WITH LOCAL TIME ZONE", "LocalDateTime" },
                { "INTERVAL YEAR TO MONTH", "String" },
                { "INTERVAL DAY TO SECOND", "String" },
                { "RAW", "byte[]" },
                { "LONG RAW", "byte[]" },
                { "BLOB", "Blob" },
                { "BFILE", "Blob" },
                { "CLOB", "Clob" },
                { "NCLOB", "Clob" },
                { "ROWID", "RowId" },
                { "UROWID", "RowId" },
                { "XMLType", "SQLXML" }
            };
        public OracleDBInfoGetter(ConnectionInfo connInfo) : base(connInfo)
        {
        }

        public override OracleConnection Connect()
        {
            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder
            {
                UserID = ConnInfo.UserName,
                Password = ConnInfo.Password,
                DataSource = $"{ConnInfo.Host}:{ConnInfo.Port}/{ConnInfo.InitDb}",
                DBAPrivilege = ConnInfo.Privilege ?? ""
            };
            return new OracleConnection(builder.ConnectionString);
        }

        public override OracleDataAdapter GetDataAdapter(string sql, IDbConnection conn)
        {
            return new OracleDataAdapter(sql, conn as OracleConnection);
        }

        public override List<string> GetDbList()
        {
            throw new NotImplementedException();
        }


        public override string GetJavaType(ColumnInfo column)
        {
            var columnType = column.Type.ToUpper();
            if (column.NumScale > 0 && columnType == "NUMBER")
            {
                return "Double";
            }
            return JavaTypeMappings.TryGetValue(columnType, out var type) ? type.ToString() : "String";
        }
        public override string GetJavaScriptType(ColumnInfo column)
        {
            var javaType = GetJavaType(column).ToLower();
            var numberTypes = new string[] { "double", "integer", "float", "short" };
            if (numberTypes.Contains(javaType))
            {
                return "number";
            }
            else if (javaType == "boolean")
            {
                return "boolean";
            }
            return "string";
        }

        public override List<string> GetSchemaList(string dbName)
        {
            List<string> list = new List<string>();
            var dt = Query("SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME");
            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["USERNAME"].ToString());
            }
            return list;
        }

        public override TableInfo GetTableInfo(string db, string schema, string table)
        {
            var tbInfo = GetTableList(schema).Single(x => x.Name == table);
            if (tbInfo == null)
            {
                throw new Exception($"表 {table} 不存在！");
            }
            string tableComment = tbInfo.Comment;
            #region 字段查询
            var dt = Query($@"
                                SELECT COLS.COLUMN_NAME,
                                       CASE
                                           WHEN INSTR(COLS.DATA_TYPE, '(') > 0 THEN
                                               SUBSTR(COLS.DATA_TYPE, 1, INSTR(COLS.DATA_TYPE, '(') - 1)
                                           ELSE
                                               COLS.DATA_TYPE
                                       END AS DATA_TYPE,
			                           COLS.DATA_LENGTH,
			                           COLS.DATA_PRECISION,
			                           COLS.DATA_SCALE,
			                           COLS.NULLABLE,
			                           CMTS.COMMENTS,
			                           COLS.DATA_DEFAULT,
			                           COLS.LAST_ANALYZED,
			                           COLS.COLUMN_ID,
                                       COLS.OWNER,
			                           (CASE WHEN ( 
					                          SELECT count(*)
					                          FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b
					                          WHERE a.TABLE_NAME = COLS.TABLE_NAME
					                            AND a.CONSTRAINT_TYPE = 'P' 
					                            AND b.TABLE_NAME = a.TABLE_NAME
					                            AND NOT b.TABLE_NAME = 'CODESMITH_EXTENDED_PROPERTIES'
					                            AND b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
					                            AND a.OWNER=COLS.OWNER
					                            AND b.COLUMN_NAME=COLS.COLUMN_NAME
				                          ) >0 THEN 1 ELSE 0 END) AS IS_PK
		                       FROM ALL_TAB_COLUMNS COLS, ALL_COL_COMMENTS CMTS
		                       WHERE     COLS.TABLE_NAME = '{table}'
					                       AND COLS.TABLE_NAME = CMTS.TABLE_NAME
					                       AND COLS.COLUMN_NAME = CMTS.COLUMN_NAME
					                       AND COLS.OWNER = CMTS.OWNER
					                       AND COLS.OWNER = '{schema}'
		                       ORDER BY COLUMN_ID
                    ");
            #endregion
            var columns = new List<ColumnInfo>();
            var primaryKeyCount = 0;
            foreach (DataRow row in dt.Rows)
            {
                string precision = row["DATA_PRECISION"].ToString();
                string scale = row["DATA_SCALE"].ToString();
                var isPk = row["IS_PK"].ToString() == "1";
                if (isPk) primaryKeyCount++;
                columns.Add(new ColumnInfo()
                {
                    Name = row["COLUMN_NAME"].ToString(),
                    Type = row["DATA_TYPE"].ToString(),
                    IsPrimaryKey = isPk,
                    IsAllowNull = row["NULLABLE"].ToString() == "Y",
                    Comment = row["COMMENTS"].ToString(),
                    NumLength = precision.IsNullOrEmpty() ? -1 : int.Parse(precision.ToString()),
                    NumScale = scale.IsNullOrEmpty() ? -1 : int.Parse(scale.ToString())
                });
            }
            return new TableInfo()
            {
                Name = table,
                TrimPrefixName = table,
                Comment = tableComment,
                Columns = columns,
                PrimaryKeyCount = primaryKeyCount
            };
        }

        public override List<dynamic> GetTableList(string parentName)
        {
            var list = new List<dynamic>();
            var dt = Query($@"SELECT T.OBJECT_NAME NAME, T.CREATED, C.COMMENTS, T.OBJECT_TYPE
		                        FROM ALL_OBJECTS T
		                        LEFT JOIN ALL_TAB_COMMENTS C ON C.TABLE_NAME = T.OBJECT_NAME
		                        WHERE T.OBJECT_TYPE IN ('TABLE', 'VIEW') and C.OWNER=T.OWNER  and C.OWNER='{parentName}' ORDER BY T.OBJECT_NAME");

            foreach (DataRow row in dt.Rows)
            {
                list.Add(new { Name = row["NAME"].ToString(), Comment = row["COMMENTS"].ToString() });
            }
            return list;
        }

        public override void TestConnection()
        {
            var conn = Connect();
            conn.Open();
            conn.Close();
        }
    }
}
